Posted by Kyle Hankinson August 12, 2020
The first thing you will need to do in order to kill or cancel a PostgreSQL query is to find the PID. This can be found by running the following query:
SELECT * FROM pg_stat_activity WHERE state = 'active';Now that you have the PID, there are two options for killing the query.
Option #1 (graceful):
SELECT pg_cancel_backend(<PID>);Option #2 (forceful):
SELECT pg_terminate_backend(<PID>);Generally, Option #1 should be used as it gracefully terminates the query. Sometimes, however the query still continues to run for a long period of time even after being gracefull terminated. This is when Option #1 should be used.
If you want to terminate all running queries, the following statement can be executed:
SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE state = 'active' and pid <> pg_backend_pid();The above statement will kill all active queries and should only be used in special situations.